
use "D:\Workdata\702525\users\Daniel\DecentralizationSorting\NewData\persondata.dta", clear

keep IE_TYPE koejd cvrnr year pnr timelon hffsp koen arledgr brutto perindkp lonind civst exper ansaar tlonkval lbnr jurnr /*
*/ aldernov pstill pb03 persbrc postnr arbkom isco88 hf_kilde postnr child06 pstill_fida sstill_fida postnr akasst arblhu

drop if year==.
destring jurnr, replace
format jurnr %12.0g
replace cvrnr=jurnr if cvrnr==.
drop jurnr

/* Correct for missing firm codes */
bysort pnr lbnr: egen temp_maxyear=max(year)
bysort pnr lbnr: egen temp_minyear=min(year)
bysort pnr cvrnr: egen temp_maxyear2=max(year)
bysort pnr cvrnr: egen temp_minyear2=min(year)
sort pnr year
replace lbnr=lbnr[_n-1] if lbnr==0&lbnr[_n-1]!=0&temp_maxyear[_n-1]>year/*
*/&pnr==pnr[_n-1]&temp_maxyear[_n-1]!=temp_maxyear&cvrnr[_n-1]==cvrnr
replace lbnr=lbnr[_n+1] if lbnr==0&lbnr[_n+1]!=0&temp_minyear[_n+1]<year/*
*/&pnr==pnr[_n+1]&temp_minyear[_n+1]!=temp_minyear&cvrnr[_n+1]==cvrnr
replace lbnr=lbnr[_n-2] if lbnr==0&lbnr[_n-2]!=0&temp_maxyear[_n-2]>year/*
*/&pnr==pnr[_n-2]&temp_maxyear[_n-2]!=temp_maxyear&cvrnr[_n-2]==cvrnr
replace lbnr=lbnr[_n+2] if lbnr==0&lbnr[_n+2]!=0&temp_minyear[_n+2]<year/*
*/&pnr==pnr[_n+2]&temp_minyear[_n+2]!=temp_minyear&cvrnr[_n+2]==cvrnr
replace lbnr=lbnr[_n-3] if lbnr==0&lbnr[_n-3]!=0&temp_maxyear[_n-3]>year/*
*/&pnr==pnr[_n-3]&temp_maxyear[_n-3]!=temp_maxyear&cvrnr[_n-3]==cvrnr
replace lbnr=lbnr[_n+3] if lbnr==0&lbnr[_n+3]!=0&temp_minyear[_n+3]<year/*
*/&pnr==pnr[_n+3]&temp_minyear[_n+3]!=temp_minyear&cvrnr[_n+3]==cvrnr

replace cvrnr=cvrnr[_n-1] if cvrnr==.&cvrnr[_n-1]!=0&lbnr[_n-1]!=.&temp_maxyear2[_n-1]>year/*
*/&pnr==pnr[_n-1]&temp_maxyear2[_n-1]!=temp_maxyear2&lbnr==lbnr[_n-1]&lbnr!=0
replace cvrnr=cvrnr[_n+1] if cvrnr==.&cvrnr[_n+1]!=0&lbnr[_n+1]!=.&temp_minyear2[_n-1]<year/*
*/&pnr==pnr[_n+1]&temp_minyear2[_n+1]!=temp_minyear2&lbnr==lbnr[_n+1]&lbnr!=0
replace cvrnr=cvrnr[_n-2] if cvrnr==.&cvrnr[_n-2]!=0&lbnr[_n-2]!=.&temp_maxyear2[_n-2]>year/*
*/&pnr==pnr[_n-2]&temp_maxyear2[_n-2]!=temp_maxyear2&lbnr==lbnr[_n-2]&lbnr!=0
replace cvrnr=cvrnr[_n+2] if cvrnr==.&cvrnr[_n+2]!=0&lbnr[_n+2]!=.&temp_minyear2[_n-2]<year/*
*/&pnr==pnr[_n+2]&temp_minyear2[_n+2]!=temp_minyear2&lbnr==lbnr[_n+2]&lbnr!=0
replace cvrnr=cvrnr[_n-3] if cvrnr==.&cvrnr[_n-3]!=0&lbnr[_n-3]!=.&temp_maxyear2[_n-3]>year/*
*/&pnr==pnr[_n-3]&temp_maxyear2[_n-3]!=temp_maxyear2&lbnr==lbnr[_n-3]&lbnr!=0
replace cvrnr=cvrnr[_n+3] if cvrnr==.&cvrnr[_n+3]!=0&lbnr[_n+3]!=.&temp_minyear2[_n-3]<year/*
*/&pnr==pnr[_n+3]&temp_minyear2[_n+3]!=temp_minyear2&lbnr==lbnr[_n+3]&lbnr!=0

/* Drop multiple observations per year */
bysort pnr year: gen temp=_N
bysort pnr year: egen temp2=sum(pstill_fida)
drop if temp2>1&pstill_fida==0&temp>1

drop temp*
merge m:1 pnr using "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\AKM\personeffs_ma.dta", keep(1 3) nogen
gen branche=1*(persbrc>=140000)*(persbrc<=169999)+2*(persbrc>=170000)*(persbrc<=209999)+3*(persbrc>=210000)*(persbrc<=229999)+ /*
*/         4*(persbrc>=230000)*(persbrc<=249999)+5*(persbrc>=250000)*(persbrc<=269999)+6*(persbrc>=270000)*(persbrc<=289999)+ /*
*/         7*(persbrc>=290000)*(persbrc<=291999)+8*(persbrc>=292000)*(persbrc<=292999)+9*(persbrc>=293000)*(persbrc<=299999)+ /*
*/         10*(persbrc>=300000)*(persbrc<=329999)+11*(persbrc>=330000)*(persbrc<=349999)+12*(persbrc>=350000)*(persbrc<=359999)+ /*
*/         13*(persbrc>=360000)*(persbrc<=399999)+14*(persbrc>=400000)*(persbrc<=449999)+15*(persbrc>=450000)*(persbrc<=452999)+ /*
*/         16*(persbrc>=453000)*(persbrc<=453999)+17*(persbrc>=454000)*(persbrc<=499999)+18*(persbrc>=500000)*(persbrc<=509999)+ /*
*/         19*(persbrc>=510000)*(persbrc<=519999)+20*(persbrc>=520000)*(persbrc<=521999)+21*(persbrc>=522000)*(persbrc<=549999)+ /*
*/         22*(persbrc>=550000)*(persbrc<=599999)+23*(persbrc>=600000)*(persbrc<=649999)+24*(persbrc>=650000)*(persbrc<=749999)+ /*
*/         25*(persbrc>=750000)*(persbrc<=979999)

compress
save "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\persondata_v2.dta", replace

use "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\persondata_v2.dta", clear

merge m:1 isco88 using "$project\occupation\occupation", keep(1 3) nogen keepus(rt1)
xtile routine=rt1, nq(3)
replace routine=0 if routine<=2
replace routine=1 if routine==3

gen fcsl=0
replace fcsl=1 if isco>=5120&isco<=5123
replace fcsl=1 if isco>=7140&isco<=7143
replace fcsl=1 if isco==9132|isco==9141|isco==9142
replace fcsl=1 if isco==9151|isco==9152
replace fcsl=1 if isco==8322|isco==8323|isco==8324

drop if cvrnr==.|lbnr==0

gen pb=persbrc
merge m:1 pb year using "$home\industrycodes\crosswalk_pb07", nogen keep(1 3)
replace pb07=persbrc if pb07==.

gen nworker=1
gen ln_hrwage=log(timelon)
gen log_income=log(lonind)
gen max_educ=int(hffsp/1000000)
gen educ=1 if max_educ<=25
replace educ=2 if max_educ==35
replace educ=3 if max_educ>=40&max_educ<=50
replace educ=4 if max_educ>=60&max_educ<.
gen sh_educ_voc=(educ==2)
gen sh_educ_high=(educ==3|educ==4)
gen sh_female=(koen==2)
gen avg_age=aldernov
gen avg_exper=exper
bysort lbnr year: gen temp_nworker=_N
bysort lbnr year pb07: egen temp_pbshare=sum(1/temp_nworker)
replace pb07=. if temp_pbshare<=0.5
collapse (sum) nworker routine fcsl (mean) ln_hrwage log_income pb07 pe_1 /*
*/ sh_educ* sh_female avg_age avg_exper, by(lbnr cvrnr year)

*drop if multiple firm in one year
bysort lbnr year: egen temp=nvals(cvrnr)
bysort lbnr: egen temp2=max(temp)
by lbnr year: egen temp3=sum(nworker)
bysort lbnr cvrnr: gen temp4=_N
drop if temp>1&nworker==1
drop if temp>1&nworker/temp3<=0.1
drop if temp>1&temp4==1
gsort lbnr year -nworker
by lbnr year: gen temp5=_n
drop if temp5>1
drop temp*

bysort cvrnr: egen firm_firstyear=min(year)
bysort cvrnr: egen firm_lastyear=max(year)
bysort lbnr: egen est_firstyear=min(year)
bysort lbnr: egen est_lastyear=max(year)

/*
preserve
rename year prevyear
rename cvrnr cvrnr_prevyear
keep prevyear lbnr cvrnr_prevyear
save "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\prevyear.dta", replace
restore

rename year nextyear
rename cvrnr cvrnr_nextyear
keep nextyear lbnr cvrnr_nextyear
save "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\nextyear.dta", replace
*/

*require continous observations (BE CAUTIOUS)
*bysort lbnr: gen nyear=_N
*drop if est_lastyear-est_firstyear!=nyear-1
*drop nyear

*Mark spurious ownership changes*
bysort lbnr cvrnr: egen temp_maxyear=max(year)
bysort lbnr cvrnr: egen temp_minyear=min(year)
sort lbnr year
by lbnr: gen spurious_change=(cvrnr!=cvrnr[_n-1]&year[_n-1]<temp_maxyear[_n-1]&cvrnr[_n-1]!=.)
by lbnr: replace spurious_change=1 if cvrnr!=cvrnr[_n-1]&year>temp_minyear&cvrnr[_n-1]!=.

cap drop _merge
gen nextyear=year+1
merge 1:1 lbnr nextyear using "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\nextyear.dta", keep(1 3) nogen
bysort cvrnr year: egen nfirm_nextyear=nvals(cvrnr_nextyear)

gen prevyear=year-1
merge 1:1 lbnr prevyear using "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\prevyear.dta", keep(1 3) nogen
rename cvrnr_prevyear temp_prevyear
replace temp_prevyear=. if spurious==1
bysort cvrnr year: egen nfirm_prevyear=nvals(temp_prevyear)
merge 1:1 lbnr prevyear using "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\prevyear.dta", keep(1 3) nogen

gen temp=(nfirm_prevyear>1&nfirm_prevyear<.)
bysort cvrnr year: egen merger=max(temp)
gen temp2=(nfirm_nextyear>1&nfirm_nextyear<.)
bysort cvrnr year: egen partial=max(temp2)
gen acquirer=(merger==1&cvrnr==cvrnr_prevyear)
bysort cvrnr year: egen nacquirer=sum(acquirer)
gen target=(merger==1&cvrnr_prevyear!=.&cvrnr!=cvrnr_prevyear&nacquirer>0)

tsset lbnr year
drop temp* nextyear prevyear nfirm* 
compress
sort lbnr year
merge 1:1 lbnr year using "$project\merger_lbnr", keep(1 3) keepus(lbnr_merger) nogen
merge m:1 pb07 using "$home\industrycodes\ind36", nogen keep(1 3)
merge m:1 pb07 using "$home\industrycodes\ind127", nogen keep(1 3)
replace ind36="X" if ind36==""
replace ind127=99999 if ind127==.

bysort cvrnr year: egen nworker_cvrnr=sum(nworker)
gen byte spinout=(cvrnr_nextyear!=cvrnr&cvrnr_nextyear!=.)
gen byte exit=(cvrnr_nextyear==.&year<2011)
bysort cvrnr year: egen sh_spinout=sum(spinout*nworker/nworker_cvrnr)
bysort cvrnr year: egen sh_exit=sum(exit*nworker/nworker_cvrnr)
save "D:\Workdata\702525\users\Daniel\DecentralizationSorting\Alex\MA\merger_lbnr", replace

/* Add Location Info */
use "$home\persondata_v2", clear
drop if cvrnr==.|lbnr==0
merge m:1 arbkom using "$home\geography\crosswalk_arbkom", keep(1 3) nogen
bysort lbnr year: gen temp_emplbnr=_N
bysort lbnr year arbkom07: egen temp_shmuni=sum(1/temp_emplbnr)
bysort lbnr year postnr: egen temp_shzip=sum(1/temp_emplbnr)
replace postnr=. if temp_shzip<=0.5
replace arbkom07=. if temp_shmuni<=0.5
collapse postnr arbkom07, by(lbnr year)
ren arbkom07 muni
merge 1:1 lbnr year using "$project\merger_lbnr", keep(2 3) nogen
save "$project\merger_lbnr", replace


/* Firm-level dataset */
use "$project\merger_lbnr", clear
sort lbnr year
by lbnr: gen temp=(target[_n+1]==1)
bysort cvrnr year: egen target_cvrnr=max(temp)
gen pe=pe_1*nworker
gen wage=ln_hrwage*nworker
gen income=log_income*nworker
bysort cvrnr year: gen nestab=_N
bysort cvrnr year: gen nestab2=sum(1-spurious)
replace ind127=. if ind127==99999
replace ind36="" if ind36=="X"
bysort cvrnr year: egen temp_nworker=sum((ind127!=.))
bysort cvrnr year ind127: egen temp_sh=sum(1*(ind127!=.)/temp_nworker)
bysort cvrnr year: egen temp_nworker2=sum((ind36!=""))
bysort cvrnr year ind36: egen temp_sh2=sum(1*(ind36!="")/temp_nworker2)
replace ind127=. if temp_sh<0.7
replace ind36="" if temp_sh2<0.7
drop temp*
bysort cvrnr year: egen temp_nworker=sum((postnr!=.))
bysort cvrnr year postnr: egen temp_sh=sum(1*(postnr!=.)/temp_nworker)
bysort cvrnr year: egen temp_nworker2=sum((muni!=.))
bysort cvrnr year muni: egen temp_sh2=sum(1*(muni!=.)/temp_nworker2)
replace muni=. if temp_sh2<0.7
replace postnr=. if temp_sh<0.7
collapse (mean) merger nestab* target_cvrnr sh_exit sh_spinout ind127 /*
*/ postnr muni (sum) nworker routine fcsl pe wage income (first) ind36, by(cvrnr year)
replace pe=pe/nworker
replace wage=wage/nworker
replace income=income/nworker
replace wage=. if wage==0
replace pe=. if pe==0
replace income=. if income==0
replace ind127=99999 if ind127==.
replace ind36="X" if ind36==""
tsset cvrnr year
by cvrnr: gen merger_cumu=sum(merger)
save "$project\merger_cvrnr", replace

